/****** Object:  StoredProcedure [dbo].[predimMergedCompany]    Script Date: 22.11.2021 13:38:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[predimMergedCompany] 
@Item [dbo].[ItemType] READONLY

AS 
BEGIN

WITH tMAXRowID
AS
(SELECT
		sACP.[CompanyPrefix]
	   ,MAX(sACP.[bigint_timestamp]) AS [RowID]
	FROM [stg$Accounting Period] sACP
	GROUP BY sACP.[CompanyPrefix])

MERGE [DW].[dCompany] AS Target USING 
(SELECT
		lCOM.[CompanyName]
	   ,lCOM.[CompanyPrefix]
	   ,MONTH(MAX(sACP.[Starting Date])) AS [FiscalYearStart]
	   ,COALESCE(tMRI.[RowID], 0) as [RowID]
	   ,GETUTCDATE() AS [BatchDate]
	FROM [dbo].[LSInsight$Companies] lCOM
	LEFT JOIN [stg$Accounting Period] sACP
		ON lCOM.[CompanyPrefix] = sACP.[CompanyPrefix]
		AND sACP.[New Fiscal Year] = 1
	LEFT JOIN [tMAXRowID] tMRI
		ON lCOM.[CompanyPrefix] = tMRI.[CompanyPrefix]
	WHERE lCOM.[Company] <> -1
	GROUP BY lCOM.[CompanyName]
			,lCOM.[CompanyPrefix]
			,tMRI.[RowID]
) AS Source

ON TARGET.[CompanyPrefix] = Source.[CompanyPrefix]

WHEN MATCHED
	THEN UPDATE
		SET [CompanyName] = Source.[CompanyName]
		   ,[CompanyPrefix] = Source.[CompanyPrefix]
		   ,FiscalYearStart = Source.FiscalYearStart
		   ,[BatchDate] = Source.[BatchDate]
		   ,[RowID] = Source.[RowID]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([CompanyName], [CompanyPrefix], [FiscalYearStart], [BatchDate], [RowID])
			VALUES (Source.[CompanyName], source.[CompanyPrefix], Source.[FiscalYearStart], Source.[BatchDate], Source.[RowID])

WHEN NOT MATCHED BY SOURCE
	AND TARGET.[SK_Company] <> -1
	THEN DELETE
;
;
END

SELECT
	'RowCount' = @@rowcount
GO

